package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CarCodesDto;

public class CarCodesDaoImp extends TmsDao implements CarCodesDao {

	public CarCodesDaoImp() throws TmsDaoException {
		super();
	}

	/**
	 * 查询所有车辆品牌车系车辆款式信息
	 * @throws TmsDaoException 
	 * 
	 */
	@Override
	public List<CarCodesDto> findCarCodesInfo() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarCodesDto dto = null;
		List<CarCodesDto> list = new ArrayList<CarCodesDto>();
		
		try {
			conn = super.getConnection();
			
			StringBuffer sql = new StringBuffer();
			sql.append("SELECT ");
			sql.append("VENDOR.ID AS VENDORID        				,");//车辆品牌id
			sql.append("BRANDSTYLE.BRANDID AS BRANDID    			,");//车系id
			sql.append("BRANDSTYLE.STYLEID  AS CARSTYLEID			,");//车辆款式id
			sql.append("VENDOR.VENDORNAME AS VENDORNAME 			,");//车辆品牌名
			sql.append("VENDOR.COUNTRY AS COUNTRY 	 				,");//车辆国别 进口 国产
			sql.append("VENDOR.FIRSTCHAR AS FIRSTCHAR 	 			,");//车辆品牌手写字母
			sql.append("BRANDSTYLE.BRANDNAME AS BRANDNAME 	 		,");//车系名
			sql.append("BRANDSTYLE.BRANDPINYIN AS BRANDPINYIN 		,");//车系拼音
			sql.append("BRANDSTYLE.BRANDFIRSTCHAR AS BRANDFIRSTCHAR ,");//车系首选字母
			sql.append("BRANDSTYLE.STYLENAME AS STYLENAME 	 		,");//车辆款式名
			sql.append("BRANDSTYLE.STYLEPINYIN AS STYLEPINYIN  		,");//车辆款式拼音
			sql.append("BRANDSTYLE.STYLEEXHAUST AS STYLEEXHAUST 	,");//排量
			sql.append("BRANDSTYLE.STYLEGEARBOX AS STYLEGEARBOX 	,");//变速箱
			sql.append("BRANDSTYLE.STYLESEARCH AS STYLESEARCH 	 	");//车辆查询。。
			sql.append("  FROM 	 ");
			sql.append(TmsTableNames.TMS_VENDOR 	 );
			sql.append(" AS  VENDOR	 ");
			sql.append("LEFT JOIN 	 ");
			sql.append("( 	 ");
			sql.append("SELECT 	 ");
			sql.append("BRAND.ID AS BRANDID, 	 		 ");
			sql.append("BRAND.vendorId AS VENDORID, 	 ");
			sql.append("STYLE.ID AS STYLEID, 	 		 ");
			sql.append("BRAND.brandName AS BRANDNAME, 	 ");
			sql.append("BRAND.pinyin AS BRANDPINYIN, 	 ");
			sql.append("BRAND.firstChar AS BRANDFIRSTCHAR, 	 ");
			sql.append("STYLE.styleName AS STYLENAME, 	 ");
			sql.append("STYLE.pinyin AS STYLEPINYIN, 	 ");
			sql.append("STYLE.exhaust AS STYLEEXHAUST, 	 ");
			sql.append("STYLE.gearbox AS STYLEGEARBOX, 	 ");
			sql.append("STYLE.search AS STYLESEARCH 	 ");
			sql.append("FROM 	 	");
			sql.append(TmsTableNames.TMS_VEHICLE_BRAND );
			sql.append("  BRAND 		");
			sql.append("  LEFT JOIN 	");
			sql.append(TmsTableNames.TMS_CAR_STYLE);
			sql.append("  STYLE 	");
			sql.append("  ON 		");
			sql.append(" STYLE.SID = BRAND.ID   	 ");
			sql.append("  ) 		 ");
			sql.append("BRANDSTYLE 	 ");
			sql.append("ON BRANDSTYLE.VENDORID = VENDOR.ID 	 ");
			

			ps = conn.prepareStatement(sql.toString());
			
			// 执行sql
			rs = ps.executeQuery();

			while (rs.next()) {
				dto = new CarCodesDto();
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setBrandName(rs.getString("BRANDNAME"));
				dto.setCarStyleName(rs.getString("STYLENAME"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		
		return list;
	}
	
	
	@Override
	public List<CarCodesDto> findCarCodesList() throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		CarCodesDto dto = null;
		List<CarCodesDto> list = new ArrayList<CarCodesDto>();
		
		try {
			conn = super.getConnection();
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT  ");
			sql.append(" VENDOR.ID AS VENDORID        , ");
			sql.append(" BRANDSTYLE.BRANDID AS BRANDID    , ");
			sql.append(" BRANDSTYLE.STYLEID  AS CARSTYLEID, ");
			sql.append(" VENDOR.VENDORNAME AS VENDORNAME , ");
			sql.append(" BRANDSTYLE.BRANDNAME AS BRANDNAME  , ");
			sql.append(" BRANDSTYLE.STYLENAME AS STYLENAME ");
			sql.append("   FROM "+TmsTableNames.TMS_VENDOR + " AS  VENDOR  ");
			sql.append(" LEFT JOIN   ");
			sql.append(" (   ");
			sql.append(" SELECT   ");
			sql.append(" BRAND.ID AS BRANDID,    ");
			sql.append(" BRAND.vendorId AS VENDORID,   ");
			sql.append(" STYLE.ID AS STYLEID,    ");
			sql.append(" BRAND.brandName AS BRANDNAME,   ");
			sql.append(" STYLE.styleName AS STYLENAME   ");
			sql.append(" FROM "+TmsTableNames.TMS_VEHICLE_BRAND+"  BRAND  ");
			sql.append("   LEFT JOIN  "+TmsTableNames.TMS_CAR_STYLE+" STYLE ON STYLE.SID = BRAND.ID   group by BRANDID ");
			sql.append("         ) BRANDSTYLE   ");
			sql.append(" ON BRANDSTYLE.VENDORID = VENDOR.ID   ");
			ps = conn.prepareStatement(sql.toString());
			// 执行sql
			rs = ps.executeQuery();
			while (rs.next()) {
				dto = new CarCodesDto();
				dto.setVendorId(rs.getString("VENDORID"));
				dto.setBrandId(rs.getString("BRANDID"));
				dto.setCarStyleId(rs.getString("CARSTYLEID"));
				dto.setVendorName(rs.getString("VENDORNAME"));
				dto.setBrandName(rs.getString("BRANDNAME"));
				dto.setCarStyleName(rs.getString("STYLENAME"));
				list.add(dto);
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
			
		} finally {
			close(rs,ps);
		}
		return list;
	}


}
